﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Windows.Forms;

namespace EmployeeSystem
{
    public partial class Form1 : Form
    {
        SqlConnection conn;
        DataTable table;
        const string connectionString = @"Data Source=(localdb)\MSSQLLocalDB;Initial Catalog=XSCJDB;Integrated Security=True";
        public Form1()
        {
            InitializeComponent();

        }

        private void button5_Click(object sender, EventArgs e)
        {
            Application.Exit();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            if (!Check() || IsSameNo(txtNo.Text))
            {
                return;
            }

            Execute($"insert into tb_Personnel values('{txtNo.Text}','{txtName.Text}', {nudSalary.Value},'{txtEvalution.Text}')");
            MessageBox.Show("信息增加成功");
            ReadData();
        }

        /// <summary>
        /// 检查是否输入合法
        /// </summary>
        /// <returns></returns>
        private bool Check()
        {
            if (string.IsNullOrWhiteSpace(txtNo.Text))
            {
                MessageBox.Show("请输入工号");
                return false;
            }
            if (string.IsNullOrWhiteSpace(txtName.Text))
            {
                MessageBox.Show("请输入姓名");
                return false;
            }
            if (string.IsNullOrWhiteSpace(txtEvalution.Text))
            {
                MessageBox.Show("请输入评价");
                return false;
            }

            return true;
        }

        /// <summary>
        /// 是否有相同工号的员工
        /// </summary>
        /// <param name="no">工号</param>
        /// <returns></returns>
        private bool IsSameNo(string no)
        {
            using (var reader = ExecuteReader($"select No from tb_Personnel where No = '{no}'"))
            {
                if (reader.Read())
                {
                    MessageBox.Show("已存在相同工号的员工");
                    return true;
                }
            }
            return false;
        }

        /// <summary>
        /// 指定工号是否不存在
        /// </summary>
        /// <param name="no">工号</param>
        /// <returns></returns>
        private bool IsNotExist(string no)
        {
            using (var reader = ExecuteReader($"select No from tb_Personnel where No = '{txtNo.Text}'"))
            {
                if (reader.Read())
                {
                    return false;
                }
            }
            MessageBox.Show($"不存在工号为 {txtNo.Text} 的员工");
            return true;
        }

        private void button2_Click(object sender, EventArgs e)
        {
            if (!Check() || IsNotExist(txtNo.Text))
            {
                return;
            }
            
            try
            {
                Execute($"update tb_Personnel set Name='{txtName.Text}', Salary={nudSalary.Value}, Evalution='{txtEvalution.Text}' where No='{txtNo.Text}'");
            }
            catch (Exception)
            {
                MessageBox.Show("数据修改失败");
                return;
            }
            
            MessageBox.Show("数据修改成功");
            ReadData();
        }

        private void button3_Click(object sender, EventArgs e)
        {
            if (string.IsNullOrWhiteSpace(txtNo.Text))
            {
                MessageBox.Show("请输入工号");
                return;
            }

            if (IsNotExist(txtNo.Text))
            {
                return;
            }

            if(MessageBox.Show("您确定要删除这条信息?", "提示", MessageBoxButtons.YesNo,MessageBoxIcon.Warning) == DialogResult.Yes)
            {
                try
                {
                    Execute($"delete tb_Personnel where No='{txtNo.Text}'");
                }
                catch (Exception)
                {
                    MessageBox.Show("数据删除失败");
                    return;
                }

                MessageBox.Show("数据删除成功");
                ReadData();
            }
        }

        private void button4_Click(object sender, EventArgs e)
        {
            txtNo.Text = "";
            txtName.Text = "";
            txtEvalution.Text = "";
            nudSalary.Value = 1;
        }

        private  void Form1_Load(object sender, EventArgs e)
        {
            conn = new SqlConnection(connectionString);
            conn.Open();
            ReadData();
        }

        private int Execute(string sql)
        {
            using (SqlCommand cmd = conn.CreateCommand())
            {
                cmd.CommandText = sql;
                return cmd.ExecuteNonQuery();
            }
        }

        private SqlDataReader ExecuteReader(string sql)
        {
            using (SqlCommand cmd = conn.CreateCommand())
            {
                cmd.CommandText = sql;
                return cmd.ExecuteReader();
            }
        }

        private void ReadData()
        {
            using (SqlCommand cmd = conn.CreateCommand())
            {
                cmd.CommandText = "select * from tb_Personnel";

                SqlDataAdapter adapter = new SqlDataAdapter() { SelectCommand = cmd };

                DataSet set = new DataSet();
                adapter.Fill(set);
                table = set.Tables[0];
                dgvStaffInfo.DataSource = table;
            }
        }

        private void dataGridView1_CellMouseClick(object sender, DataGridViewCellMouseEventArgs e)
        {
            object[] array = table.Rows[e.RowIndex].ItemArray;
            txtNo.Text = array[0].ToString();
            txtName.Text = array[1].ToString();
            nudSalary.Value = int.Parse(array[2].ToString());
            txtEvalution.Text = array[3].ToString();
        }
    }
}
